* Table 12 
local table "Table12"
use "./data/academics.dta", clear
createvarsacad
keep if tenure
qui putexcel set "./tables/tables.xlsx", sheet("`table'") modify
gen publisher=person_id<.
gen foreign=nation!=620 /* "620=PT" */
gen female=sex==1
gen phdpt=phdctry==620
gen phdus=phdctry==840
gen phduk=phdctry==826
qui putexcel A1=("Univ")
qui putexcel B1=("N")
qui putexcel C1=("% Fem")
qui putexcel D1=("% For")
qui putexcel E1=("% PT")
qui putexcel F1=("% US")
qui putexcel G1=("% UK")
qui putexcel H1=("% Pub")
qui putexcel I1=("Rot")
local pos=2
decode mainaff,gen(estab)
splitstr dum estab
foreach name in ///
"U LISBOA" "U NOVA" "U PORTO" "U MINHO" "U COIMBRA" /// 
"ISCTE IUL" "U CATÓLICA PORTUGUESA" "U ÉVORA" "U BEIRA INTERIOR" "U AVEIRO" "U ALGARVE" ///
"U AÇORES" "U MADEIRA" "UTAD" {
qui putexcel A`pos'=("`name'")
unique name if estab=="`name'"
local N=r(unique)
unique name if estab=="`name'" & female==1
local female=r(unique)
local shfem=`female'/`N'
unique name if estab=="`name'" & foreign==1
local foreign=r(unique)
local shfor=`foreign'/`N'
unique name if estab=="`name'" & phdpt==1
local phdpt=r(unique)
local shppt=`phdpt'/`N'
unique name if estab=="`name'" & phdus==1
local phdus=r(unique)
local shpus=`phdus'/`N'
unique name if estab=="`name'" & phduk==1
local phduk=r(unique)
local shpuk=`phduk'/`N'
unique name if estab=="`name'" & publish==1
local publish=r(unique)
local shpub=`publish'/`N'
*
count if estab=="`name'"
local rot=`N'/(r(N)/18) 
*
qui putexcel B`pos'=(`N'), nformat(#)
qui putexcel C`pos'=(`shfem'), nformat(#.0%)
qui putexcel D`pos'=(`shfor'), nformat(#.0%)
qui putexcel E`pos'=(`shppt'), nformat(#.0%)
qui putexcel F`pos'=(`shpus'), nformat(#.0%)
qui putexcel G`pos'=(`shpuk'), nformat(#.0%)
qui putexcel H`pos'=(`shpub'), nformat(#.0%)
qui putexcel I`pos'=(`rot'), nformat(#.0)
local ++pos
}
qui putexcel A`pos'=("All")
unique name
local N=r(unique)
unique name if female==1
local female=r(unique)
local shfem=`female'/`N'
unique name if foreign==1
local foreign=r(unique)
local shfor=`foreign'/`N'
unique name if phdpt==1
local phdpt=r(unique)
local shppt=`phdpt'/`N'
unique name if phdus==1
local phdus=r(unique)
local shpus=`phdus'/`N'
unique name if phduk==1
local phduk=r(unique)
local shpuk=`phduk'/`N'
unique name if publish==1
local publish=r(unique)
local shpub=`publish'/`N'
count
local rot=`N'/(r(N)/18)
qui putexcel B`pos'=(`N'), nformat(#.0)
qui putexcel C`pos'=(`shfem'), nformat(#.0%)
qui putexcel D`pos'=(`shfor'), nformat(#.0%)
qui putexcel E`pos'=(`shppt'), nformat(#.0%)
qui putexcel F`pos'=(`shpus'), nformat(#.0%)
qui putexcel G`pos'=(`shpuk'), nformat(#.0%)
qui putexcel H`pos'=(`shpub'), nformat(#.0%)
qui putexcel I`pos'=(`rot'), nformat(#.0)
